package com.mexcad.db.connectors;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mexcad.db.utils.ExceptionDB;

import com.mexcad.db.dto.CKGerente;
import com.mexcad.db.dto.CatEsquemaCredito;
import com.mexcad.db.dto.CatEstado;
import com.mexcad.db.dto.Cliente;
import com.mexcad.db.dto.Gerente;
import com.mexcad.db.utils.HibernateUtil;
import com.mexcad.utils.Formatter;

public class ClienteConnector {

	public static final int MEXCAD=1;
	
	public static final int GUARDAR=0;
	public static final int EDITAR=1;
	public static final int FIRMAR=2;
	private static Log logger = LogFactory.getLog(ClienteConnector.class);
	//private List<BienesSolicitadosDetalle> listaDetalles;
	//private List<BienesSolCvePtales> listaCvesPtales;

	public ClienteConnector(){
		logger.debug("Instanciating ClienteConnector");
	}
	
	
	
	
/*	public static Afectaciones creaInstanciaAfectaciones(){
/		Afectaciones afect = new Afectaciones();
		return afect;
	}
	*/
	
	
	public static String getNombreCliente(int idCliente){
		
		Session session = HibernateUtil.getSessionFactory().openSession();
		String cliente=null;
		try{
			String query ="select nombreCliente FROM Cliente where idCliente="+idCliente ;
			 cliente=(String)session.createQuery(query).uniqueResult();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return cliente;	
	}
	
	
	public static List<Cliente> getAllClientes(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Cliente>lista=null;
		try{
			String query ="FROM Cliente order by nombreCliente";
			lista=session.createQuery(query).list();
			//System.out.println(lista);
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;	
	}

	public static List<Cliente> getAllClientesConGerentes(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM Cliente where idGerente <> 0 order by nombreCliente";
		List <Cliente>lista=session.createQuery(query).list();
		//System.out.println(lista);
		session.close();
		return lista;	
	}
	
	public static double getTasaBaseByCliente(int idCliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query =" select tasaBase FROM  Cliente where ckCliente.idCliente="+idCliente;
		Double cliente=(Double)session.createQuery(query).uniqueResult();
		//System.out.println(lista);
		session.close();
		return cliente.doubleValue();	
	}
	
	
	
	public static List <Cliente> getAllClientesHoja(){
		
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Cliente>lista=null;
		try{	
			String query =" FROM Cliente where hojaArbol=true order by nombreCliente";
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;
	}
	
	public static List <Cliente> getAllClientesHojaByEstado(int idEstado){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Cliente>lista=null;
		try{
			String query =" FROM Cliente where idEstado="+idEstado +" AND idGerente <> 0 order by nombreCliente";
			lista=session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;
	}
	public static int getParentCount(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Integer num = null;
		try{
			String query ="SELECT  COUNT( DISTINCT  nodoPadre)  FROM  Cliente";
			num = (Integer)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return num;
	}
	
	public static int getStateCount(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Integer num = null;
		try{
			String query ="SELECT  COUNT( DISTINCT  idEstado)  FROM  Cliente";
			num = (Integer)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return num;
	}

	public static int getStateCountByPadreAndEstado(int nodoPadre, int estado){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Integer num=null;
		try{
			String query ="SELECT  COUNT( DISTINCT  idCliente)  FROM  Cliente where nodoPadre="+nodoPadre+" AND idEstado="+estado ;
			num = (Integer)session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return num;
	}

	
	
	
	public static List<CatEstado> getAllEstados(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <CatEstado>lista=null;
		try{
			String query =" FROM  CatEstado";
			lista= session.createQuery(query).list();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;
	}
	
	public static List<Cliente> getClientesPorEstado(int idEstado){
		Session session = HibernateUtil.getSessionFactory().openSession();
		List <Cliente>lista=null;
		try{
			String query =" FROM  Cliente where idEstado="+idEstado+ " order by nombreCliente";
			lista= session.createQuery(query).list();
			session.close();
			
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		return lista;
	}
	
	public static boolean isGerenteAssignedById(int idCliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Integer res=null;
		try{
			String query =" select idGerente FROM  Cliente where ckCliente.idCliente="+idCliente;
			res= (Integer) session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null)
				session.close();
			
		}catch(Exception e){
			if(session!= null)
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		if(res==0)
			return false;
		else
			return true;
	}
	
	public static void actualizaLasComisionesHijos(Cliente cliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = session.beginTransaction();
			
		try{	
			logger.debug("Querying actualizaLasComisionesHijos");
			String query = "update Cliente set comisionMexcad="+cliente.getComisionMexcad()+", comisionGerente="+cliente.getComisionGerente()+", comisionAcreedor="+cliente.getComisionAcreedor()+" where nodoPadre="+cliente.getCkCliente().getIdCliente() ;
		
			session.createQuery(query).executeUpdate();
			
			transaction.commit();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			
		}catch(Exception e){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			
		} finally{
			if(session!= null && session.isOpen())
				session.close();
			
		}
		
	}
	
	
	public static synchronized int guardaCliente(Cliente cliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction transaction = session.beginTransaction();
	
		try{
			if(cliente.getNombreCliente() != null)
				cliente.setNombreCliente(cliente.getNombreCliente().toUpperCase());
			
			if(cliente.getDireccion() != null)
				cliente.setDireccion(cliente.getDireccion().toUpperCase());
			
			if(cliente.getRfc() != null)
				cliente.setRfc(cliente.getRfc().toUpperCase());
			
			if(cliente.getNodoPadre() != MEXCAD){
				
				if(cliente.getNodoPadre()==0){
					cliente.setHojaArbol(false);
					if(cliente.getCkCliente().getIdCliente() != 0 && cliente.getCkCliente().getIdCliente() != MEXCAD){
						actualizaLasComisionesHijos(cliente);
						
					}
					
				}else{
					Cliente c = ClienteConnector.getClienteById(cliente.getNodoPadre());
					cliente.setComisionMexcad(c.getComisionMexcad());
					cliente.setComisionAcreedor(c.getComisionAcreedor());
					cliente.setComisionGerente(c.getComisionGerente());
					cliente.setHojaArbol(true);
					
				}
			}else{
				if(cliente.getNodoPadre()==0){
					cliente.setHojaArbol(false);
				}else{
					cliente.setHojaArbol(true);
				}
			}
			
			
			
			
			cliente.setFechaCreacion( Formatter.getFechaHoraActual());
			logger.debug("Querying Cliente");
			if(cliente.getCkCliente().getIdCliente() == 0){
				cliente.getCkCliente().setIdCliente(getUltimoIdCliente());
			}
			session.saveOrUpdate(cliente);
			transaction.commit();

			session.close();
			Gerente gerente = cliente.getGerente();
			if(gerente!=null){
				if(gerente.getCkGerente()==null){
					gerente.setCkGerente(new CKGerente());
				}
				
				gerente.getCkGerente().setIdCliente(cliente.getCkCliente().getIdCliente());
				GerenteConnector.guardaGerenteActualizaCliente(gerente);
				
			}
			return cliente.getCkCliente().getIdCliente();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			transaction.rollback();
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(e);
		}finally{
			if(session!= null&& session.isOpen())
				session.close();
		} 
		
	}
	
	public static   int getUltimoIdCliente(){
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query = "select max(ckCliente.idCliente) from Cliente ";
		Integer folio=null;
		try{
			folio = (Integer) session.createQuery(query).uniqueResult();
			session.close();
			
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(excepts);
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
			return ExceptionDB.getNumMessageDB(e);
		}finally{
			if(session!= null&& session.isOpen())
				session.close();
		} 
		
		if(folio==null)
			return 1;
		else
			return folio+1;
		
	}
	
	public static   Cliente getClienteById(int idCliente){
		Session session = HibernateUtil.getSessionFactory().openSession();
			String query = "from Cliente where idCliente="+idCliente;
			Cliente cliente =null;
		try{
			cliente = (Cliente) session.createQuery(query).uniqueResult();
			session.close();
		}catch(org.hibernate.exception.GenericJDBCException excepts){
			if(session!= null&& session.isOpen())
				session.close();
		}catch(Exception e){
			if(session!= null&& session.isOpen())
				session.close();
		}finally{
			if(session!= null&& session.isOpen())
				session.close();
		} 
		return cliente;
		
	}
	
	
	
	/*
	public static CtCvesProgoPerAnualNER getCtCvesProgoPerAnualByProgNER(CtClavesPoaPK pkAfec,String re, String rs, String actividad){
		logger.debug("Querying CtCvesProgoPerAnual");
		Session session = HibernateUtil.getSessionFactory().openSession();
		String query ="FROM CtCvesProgoPerAnualNER where anio="+pkAfec.getAnioId()+" AND "+
						"sector='"+pkAfec.getSectorId()+"' AND  "+
						"subsector='"+pkAfec.getSubsectorId()+"' AND  "+
						"unidadr='"+pkAfec.getUnidadId()+"' AND  "+
						"re='"+re+"' AND  "+
						"sr='"+rs+"' AND " +
						"ai='"+actividad+"'" ;
		
		CtCvesProgoPerAnualNER clavePOA=(CtCvesProgoPerAnualNER)session.createQuery(query).uniqueResult();
		session.close();	
		
		return clavePOA;
	}



   public static List<AfectacionesStatus> getAllAfectacionesStatus(){
			logger.debug("Querying AfectacionesStatus");
			Session session = HibernateUtil.getSessionFactory().openSession();
			String query ="FROM AfectacionesStatus";
			List <AfectacionesStatus>lista=session.createQuery(query).list();
			return lista;
   }
   
*/
	public static void main(String []arg){
		System.out.println("parentCount: "+ClienteConnector.getStateCount());
	}
	
}
